package com.bingo.mpa.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.bingo.mpa.pojo.User;

public class UserDao
{
	public static void save( User obj ) throws SQLException
    {
		final Connection conn = ConnectionPool.getConnection();
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO P_USER(AID, SID, ACCOUNT,NAME,MOBILE,ADDRESS,ROLE,STATUS,REG_TIME)" + " VALUES(?,?,?,?,?,?,?,?,?)";
        int j = 1;
        try
        {
        	conn.setAutoCommit(false);
        	pstmt = conn.prepareStatement(sql.toString());
        	pstmt.setLong(j++, obj.getAid());
        	pstmt.setLong(j++, obj.getSid());
        	pstmt.setString(j++, obj.getAccount());
        	pstmt.setString(j++, obj.getName());
        	pstmt.setString(j++, obj.getMobile());
        	pstmt.setString(j++, obj.getAddress());
        	pstmt.setByte(j++, obj.getRole());
        	pstmt.setByte(j++, obj.getStatus());
        	pstmt.setTimestamp(j++, obj.getReg_time());
        	pstmt.execute();
        	conn.commit();
        }
        catch(Exception e)
        {
        	e.printStackTrace();
        }
        finally
        {
        	try
        	{
        		if(pstmt != null)pstmt.close();
        		if(conn != null)
        		{
        			conn.setAutoCommit(true);
        			conn.close();
        		}
        	}
        	catch(Exception ex)
        	{
        		ex.printStackTrace();
        	}
        }
    }
	
	public static long getSeq()
	{
		long seq = 0L;
		final Connection conn = ConnectionPool.getConnection();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		
		try
		{
			cstmt = conn.prepareCall("{CALL getSeq(?)}");
			cstmt.setInt(1,3);
			rs = cstmt.executeQuery();
			if(rs.next())
			{
				seq = rs.getLong(1);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			if(rs != null)
				rs.close();
			if(cstmt != null)
				cstmt.close();
			if(conn != null)
				conn.close();
			}catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return seq;
	}
	
	public static long getSeq(int type)
	{
		long seq = 0L;
		final Connection conn = ConnectionPool.getConnection();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		
		try
		{
			cstmt = conn.prepareCall("{CALL getSeq(?)}");
			cstmt.setInt(1,type);
			rs = cstmt.executeQuery();
			if(rs.next())
			{
				seq = rs.getLong(1);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			if(rs != null)
				rs.close();
			if(cstmt != null)
				cstmt.close();
			if(conn != null)
				conn.close();
			}catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return seq;
	}
	
	public static User findBySid(long sid)
	{
		final Connection conn = ConnectionPool.getConnection();
        if(conn == null )
        {
            return null;
        }
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
        	conn.setAutoCommit(false);
            String sql = "SELECT * FROM P_USER WHERE SID=?";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1,sid);
            rs = stmt.executeQuery();

            if(rs.next())
            {
                User obj = new User();
                obj.setUid(rs.getLong("UID"));
                obj.setSid(rs.getLong("SID"));
                obj.setAid(rs.getLong("AID"));
                obj.setName(rs.getString("NAME"));
                obj.setMobile(rs.getString("MOBILE"));
                return obj;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( rs != null )
                    rs.close();
                if( stmt != null )
                    stmt.close();
                if(conn != null)
                {
                	conn.close();
                }
            }
            catch (SQLException ex)
            {
            }
        }
        return null;
	}
}
